/**
 * 
 */
package it.gommonauti.patenti.dao;

import it.gommonauti.patenti.dao.factory.H2DbDAOFactory;
import it.gommonauti.patenti.entity.Category;
import it.gommonauti.patenti.entity.Exam;
import it.gommonauti.patenti.entity.Image;
import it.gommonauti.patenti.entity.Language;
import it.gommonauti.patenti.entity.MainQuestion;
import it.gommonauti.patenti.entity.SubQuestion;
import it.gommonauti.patenti.entity.UserResponse;
import it.gommonauti.patenti.exception.ApplicationException;
import it.gommonauti.patenti.exception.ApplicationException.ExceptionTypes;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

/**
 * @author Francesco Stallone ( f.stallone73@gmail.com ).
 * 
 * Data access object to retrieve MainQuestion objects from an H2 db.
 *
 */
public class H2DbMainQuestionDao implements MainQuestionDAO {

	/**
	 * Exam by id statement and language
	 */
	private static final String examByIdAndLanguageStatement =
			" 	SELECT " 
					+ "		EX.ID, "
					+ "		EX.EXAM, "
					+ "		EX.ID_LANGUAGE, "
					+ "		EX.TIME_IN_SECONDS, "
					+ "		EX.MAX_ERRORS, " 
					+ "		EX.ENABLED "
					+ "	FROM " 
					+ "		EXAMS EX "
					+ "	WHERE "
					+ "		EX.ID = ? "
					+ " AND "
					+ "		EX.ID_LANGUAGE = ? ";

	/**
	 * Main question by id statement
	 */
	private static final String mainQuestionByIdAndLanguageStatement = 
			"SELECT " 
					+ " MAIN.ID, MAIN.MAIN_QUESTION, MAIN.ID_IMAGE, MAIN.ID_LANGUAGE, MAIN.ID_CATEGORY, "     
					+ " LAN.LANGUAGE, " 
					+ " IMG.IMAGE, " 
					+ " SUB.ID ID_SUB_QUESTION, SUB.SUB_QUESTION, SUB.RESPONSE, " 
					+ " CAT.CATEGORY " 
					+ " FROM MAIN_QUESTIONS MAIN, " 
					+ " LANGUAGES LAN, " 
					+ " IMAGES IMG, " 
					+ " CATEGORIES CAT, "  
					+ " SUB_QUESTIONS SUB " 
					+ " WHERE  " 
					+ " 	MAIN.ID = ? AND MAIN.ID_LANGUAGE = ? "  
					+ " AND "   
					+ " 	MAIN.ID_LANGUAGE = LAN.ID AND MAIN.ID_LANGUAGE = IMG.ID_LANGUAGE (+) AND MAIN.ID_LANGUAGE = SUB.ID_LANGUAGE AND MAIN.ID_LANGUAGE = CAT.ID_LANGUAGE  " 
					+ " AND "    
					+ " 	MAIN.ID_IMAGE = IMG.ID (+) AND MAIN.ID_CATEGORY = CAT.ID AND SUB.ID_MAIN_QUESTION = MAIN.ID	 ";

	/**
	 * Main question by id statement
	 */
	private static final String mainQuestionsIDLinearByLanguageAndExamStatement = 
			"SELECT " 
					+ " 	MAIN.ID " 
					+ " FROM "
					+ "	MAIN_QUESTIONS MAIN, "
					+ "   CATEGORIES CATEG "
					+ " WHERE  " 
					+ " 	MAIN.ID_LANGUAGE = ? "
					+ " AND "
					+ " 	MAIN.ID_CATEGORY = CATEG.ID"
					+ " AND "
					+ " 	CATEG.ID_EXAM = ? "
					+ " ORDER BY "
					+ "	MAIN.ID ASC ";
	
	/**
	 * Main question by id statement
	 */
	private static final String mainQuestionsIDRandomByLanguageAndExamStatement = 
			"SELECT " 
					+ " 	MAIN.ID " 
					+ " FROM "
					+ "	MAIN_QUESTIONS MAIN, "
					+ "   CATEGORIES CATEG "
					+ " WHERE  " 
					+ " 	MAIN.ID_LANGUAGE = ? "
					+ " AND "
					+ " 	MAIN.ID_CATEGORY = CATEG.ID"
					+ " AND "
					+ " 	CATEG.ID_EXAM = ? "
					+ " ORDER BY "
					+ "		RAND()";

	private String categoriesByLanguageAndExamStatement = 
			"SELECT " 
					+ " 	CATEG.ID, " 
					+ " 	CATEG.QUESTION_IN_EXAM, "
					+ " 	CATEG.CATEGORY, "
					+ " 	CATEG.ID_EXAM, "
					+ " 	CATEG.MAX_ERRORS, "
					+ " 	CATEG.ID_LANGUAGE "
					+ " FROM CATEGORIES CATEG " 
					+ " WHERE  " 
					+ " 	CATEG.ID_LANGUAGE = ? "
					+ " AND "
					+ " 	CATEG.ID_EXAM = ? "
					+ " ORDER BY "
					+ " 	CATEG.ID ASC ";

	private String examsByLanguageStatement = 
			"SELECT " 
					+ " 	EX.ID, " 
					+ " 	EX.EXAM, "
					+ " 	EX.ENABLED, "
					+ " 	EX.ID_LANGUAGE "
					+ " FROM EXAMS EX " 
					+ " WHERE  " 
					+ " 	EX.ID_LANGUAGE = ? "
					+ " ORDER BY "
					+ " 	EX.ID ASC ";

	private String mainQuestionsIDRandomByIDCategoryAndIDLanguageStatement = 
			"SELECT "  
					+ "		MAIN.ID "
					+ " FROM " 
					+ "		MAIN_QUESTIONS MAIN "					   	
					+ " WHERE "  					  
					+ "   	MAIN.ID_CATEGORY = ? "
					+ " AND "
					+ "     MAIN.ID_LANGUAGE = ? "
					+ " ORDER BY RAND() ";

	public MainQuestion getMainQuestion(int id, String idLanguage) throws ApplicationException, SQLException {

		MainQuestion mainQuestion = null;

		Connection conn = null;

		ResultSet rs = null;

		try{

			conn = H2DbDAOFactory.createConnection();

			PreparedStatement ps;

			ps = conn.prepareStatement(mainQuestionByIdAndLanguageStatement);

			int idx = 1;
			ps.setInt(idx++, id);
			ps.setString(idx++, idLanguage);

			rs = ps.executeQuery();

			if (!rs.next()) throw new ApplicationException(ExceptionTypes.NO_DATA);

			// allocate result set indipendent objects 
			mainQuestion = new MainQuestion();

			Language language = new Language();
			mainQuestion.setLanguage(language);

			Category category = new Category();
			category.setLanguage(language);
			mainQuestion.setParent(category);

			Image image = new Image();
			mainQuestion.setImage(image);
			image.setLanguage(language);

			ArrayList<SubQuestion> subQuestions = new ArrayList<SubQuestion>();
			mainQuestion.setSubQuestions(subQuestions);

			boolean firstIteration = true;

			do {

				// setting common attributes
				if (firstIteration){

					firstIteration = false;

					// main question
					mainQuestion.setId(rs.getInt("ID"));
					mainQuestion.setDescription(rs.getString("MAIN_QUESTION"));

					// language
					language.setId(rs.getString("ID_LANGUAGE"));
					language.setDescription(rs.getString("LANGUAGE"));

					// image
					image.setId(rs.getInt("ID_IMAGE"));
					
					Blob mainQuestionImageBlob = rs.getBlob("IMAGE");

					if( mainQuestionImageBlob != null){

						byte[] byteArray = null;
						try {

							long len = mainQuestionImageBlob.length();
							int intLen = (int) len;
							byteArray = mainQuestionImageBlob.getBytes(1, intLen );
							Bitmap bitmap = BitmapFactory.decodeByteArray(byteArray, 0, byteArray.length/*, opts*/);
							image.setBitmap(bitmap);

						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}

					}

					// category
					category.setId(rs.getInt("ID_CATEGORY"));
					category.setDescription(rs.getString("CATEGORY"));

				}

				SubQuestion subQuestion = new SubQuestion();
				subQuestions.add(subQuestion);
				subQuestion.setParent(mainQuestion);
				subQuestion.setLanguage(language);
				subQuestion.setId(rs.getInt("ID_SUB_QUESTION"));
				subQuestion.setDescription(rs.getString("SUB_QUESTION"));
				subQuestion.setResponse(rs.getBoolean("RESPONSE"));
				subQuestion.setUserResponse(UserResponse.NOT_AVAILABE);

			} while(rs.next());

		}finally {

			// add application code here
			if (conn != null) conn.close();   
		}
		return mainQuestion;
	}


	public int[] getMainQuestionsIDLinear(String idLanguage, int idExam)
			throws ApplicationException, SQLException {

		int[] mainQuestions = null;

		Connection conn = null;

		ResultSet rs = null;

		try{

			conn = H2DbDAOFactory.createConnection();

			PreparedStatement ps;

			ps = conn.prepareStatement(mainQuestionsIDLinearByLanguageAndExamStatement,  ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			int idx = 1;
			ps.setString(idx++, idLanguage);
			ps.setInt(idx++, idExam);

			rs = ps.executeQuery();

			if (!rs.next()) throw new ApplicationException(ExceptionTypes.NO_DATA);

			rs.last();
			int count = rs.getRow();

			rs.first();

			mainQuestions = new int[count];
			int index = 0;		

			do {

				int id = rs.getInt("ID");
				mainQuestions[index++] = id;

			} while(rs.next());

		}finally {

			// add application code here
			if (conn != null) conn.close();   
		}
		return mainQuestions;
	}
	
	public int[] getMainQuestionsIDRandom(String idLanguage, int idExam)
			throws ApplicationException, SQLException {

		int[] mainQuestions = null;

		Connection conn = null;

		ResultSet rs = null;

		try{

			conn = H2DbDAOFactory.createConnection();

			PreparedStatement ps;

			ps = conn.prepareStatement(mainQuestionsIDRandomByLanguageAndExamStatement,  ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			int idx = 1;
			ps.setString(idx++, idLanguage);
			ps.setInt(idx++, idExam);

			rs = ps.executeQuery();

			if (!rs.next()) throw new ApplicationException(ExceptionTypes.NO_DATA);

			rs.last();
			int count = rs.getRow();

			rs.first();

			mainQuestions = new int[count];
			int index = 0;		

			do {

				int id = rs.getInt("ID");
				mainQuestions[index++] = id;

			} while(rs.next());

		}finally {

			// add application code here
			if (conn != null) conn.close();   
		}
		return mainQuestions;
	}


	public ArrayList<Category> getCategories(String idLanguage, int idExam)
			throws ApplicationException, SQLException {

		ArrayList<Category> categories = null;

		Connection conn = null;

		ResultSet rs = null;

		try{

			conn = H2DbDAOFactory.createConnection();

			PreparedStatement ps;

			ps = conn.prepareStatement(categoriesByLanguageAndExamStatement,  ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			int idx = 1;
			ps.setString(idx++, idLanguage);
			ps.setInt(idx++, idExam);

			rs = ps.executeQuery();

			if (!rs.next()) throw new ApplicationException(ExceptionTypes.NO_DATA);

			categories = new ArrayList<Category>();

			do {

				Category category = new Category();
				int id = rs.getInt("ID");
				String description = rs.getString("CATEGORY"); 
				String lan = rs.getString("ID_LANGUAGE");

				Exam exam = new Exam();
				exam.setId(rs.getInt("ID_EXAM"));
				category.setExam(exam);
				category.setDescription(description);
				category.setId(id);
				Language language = new Language();
				language.setId(lan); // TODO get language description
				category.setLanguage(language);
				
				category.setMaxErrors(rs.getInt("MAX_ERRORS"));
				
				category.setQuestionInExam(rs.getInt("QUESTION_IN_EXAM"));

				categories.add(category);

			} while(rs.next());

		}finally {

			// add application code here
			if (conn != null) conn.close();   
		}
		return categories;

	}


	public int[] getMainQuestionsIDRandomByIDCategoryAndIDLanguage(int idCategory,String idLanguage) 
			throws ApplicationException, SQLException {

		int[] mainQuestions = null;

		Connection conn = null;

		ResultSet rs = null;

		try{

			conn = H2DbDAOFactory.createConnection();

			PreparedStatement ps;

			ps = conn.prepareStatement(mainQuestionsIDRandomByIDCategoryAndIDLanguageStatement,  ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			int idx = 1;
			ps.setInt(idx++, idCategory);
			ps.setString(idx++, idLanguage);

			rs = ps.executeQuery();

			if (!rs.next()) throw new ApplicationException(ExceptionTypes.NO_DATA);

			rs.last();
			int count = rs.getRow();

			rs.first();

			mainQuestions = new int[count];
			int index = 0;		

			do {

				int id = rs.getInt("ID");
				mainQuestions[index++] = id;

			} while(rs.next());

		}finally {

			// add application code here
			if (conn != null) conn.close();   
		}
		return mainQuestions;
	}


	public ArrayList<Exam> getExams(String idLanguage)
			throws ApplicationException, SQLException {

		ArrayList<Exam> exams = null;

		Connection conn = null;

		ResultSet rs = null;

		try{

			conn = H2DbDAOFactory.createConnection();

			PreparedStatement ps;

			ps = conn.prepareStatement(examsByLanguageStatement,  ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			int idx = 1;
			ps.setString(idx++, idLanguage);

			rs = ps.executeQuery();

			if (!rs.next()) throw new ApplicationException(ExceptionTypes.NO_DATA);

			exams = new ArrayList<Exam>();

			do {

				Exam exam = new Exam();
				int id = rs.getInt("ID");
				String description = rs.getString("EXAM"); 
				String lan = rs.getString("ID_LANGUAGE");
				boolean enabled = rs.getBoolean("ENABLED");		

				exam.setDescription(description);
				exam.setId(id);
				Language language = new Language();
				language.setId(lan); // TODO get language description
				exam.setLanguage(language);
				exam.setEnabled(enabled);

				exams.add(exam);

			} while(rs.next());

		}finally {

			// add application code here
			if (conn != null) conn.close();   
		}
		return exams;

	}

	/**
	 * Get exam by ID an Language.
	 */
	public Exam getExam(int id, String idLanguage) throws ApplicationException, SQLException {

		Exam exam = null;

		Connection conn = null;

		ResultSet rs = null;

		try{

			conn = H2DbDAOFactory.createConnection();

			PreparedStatement ps;

			ps = conn.prepareStatement(examByIdAndLanguageStatement);

			int idx = 1;
			ps.setInt(idx++, id);
			ps.setString(idx++, idLanguage);

			rs = ps.executeQuery();

			if (!rs.next()) throw new ApplicationException(ExceptionTypes.NO_DATA);

			exam = new Exam();

			Language language = new Language();
			exam.setLanguage(language);
			language.setId(rs.getString("ID_LANGUAGE"));
			
			String description = rs.getString("EXAM");
			exam.setDescription(description);
			
			exam.setId(rs.getInt("ID"));
			
			exam.setMaxErrors(rs.getInt("MAX_ERRORS"));
			
			exam.setTimeInSeconds(rs.getInt("TIME_IN_SECONDS"));
			
			exam.setEnabled(rs.getBoolean("ENABLED"));

		}finally {

			// add application code here
			if (conn != null) conn.close();   
		}
		return exam;
	}
}
